﻿using Dapper;
using JX.Core;
using JX.Core.Entity;
using System;
using System.Collections.Generic;
using System.Text;

namespace JX.Dapper
{
	public partial class RegionRepositoryDapper : RepositoryDapper<RegionEntity>, IRegionRepositoryDapper
	{
		/// <summary>
		/// 检查区县是否存在
		/// </summary>
		/// <param name="province"></param>
		/// <param name="city"></param>
		/// <param name="area"></param>
		/// <returns></returns>
		public bool AreaExists(string province, string city, string area)
		{
			return IsExist(" and Province=@Province and City=@City and Area=@Area",new { Province = province, City = city, Area = area });
		}
		/// <summary>
		/// 检查街道是否存在
		/// </summary>
		/// <param name="province"></param>
		/// <param name="city"></param>
		/// <param name="area"></param>
		/// <param name="area1"></param>
		/// <returns></returns>
		public bool Area1Exists(string province, string city, string area, string area1)
		{
			return IsExist(" and Province=@Province and City=@City and Area=@Area and Area1=@Area1", new { Province = province, City = city, Area = area , Area1 = area1 });
		}
		/// <summary>
		/// 检查小区是否存在
		/// </summary>
		/// <param name="province"></param>
		/// <param name="city"></param>
		/// <param name="area"></param>
		/// <param name="area1"></param>
		/// <param name="area2"></param>
		/// <returns></returns>
		public bool Area2Exists(string province, string city, string area, string area1, string area2)
		{
			return IsExist(" and Province=@Province and City=@City and Area=@Area and Area1=@Area1 and Area2=@Area2", new { Province = province, City = city, Area = area, Area1 = area1, Area2 = area2 });
		}

		/// <summary>
		/// 通过邮政编码的前2位来判断是否存在相同的邮政编码。
		/// </summary>
		/// <param name="postCode"></param>
		/// <returns></returns>
		public bool PostCodeExists(string postCode)
		{
			string strSQL = "SELECT RegionID FROM Region WHERE LEFT(PostCode, 2) = LEFT(@PostCode, 2)";
			int count = GetBySQL<int>(strSQL, new { PostCode = postCode });
			return (count > 0) ? true : false;
		}

		/// <summary>
		/// 得到符合条件的邮政编码
		/// </summary>
		/// <param name="country"></param>
		/// <param name="province"></param>
		/// <param name="city"></param>
		/// <param name="area"></param>
		/// <returns></returns>
		public string GetZipCodeByArea(string country, string province, string city, string area)
		{
			return GetScalar<string>("postcode", " and country = @Country AND province = @Province AND city = @City AND area = @Area", new { Country = country, Province = province, City = city, Area = area });
		}

		/// <summary>
		/// 通过邮政编码得到实体类
		/// </summary>
		/// <param name="postCode"></param>
		/// <returns></returns>
		public RegionEntity GetByPostCodeOfFourNumber(string postCode)
		{
			string strSQL;
			if (postCode.Substring(0, 4) == "9990")
			{
				strSQL = " and LEFT(PostCode, 6) = LEFT(@PostCode, 6)";
			}
			else
			{
				strSQL = " and LEFT(PostCode, 4) = LEFT(@PostCode, 4)";
			}
			return Get(strSQL, new { PostCode = postCode });
		}

		/// <summary>
		/// 得到所有国家的列表
		/// </summary>
		/// <returns></returns>
		public IList<RegionEntity> GetCountryList()
		{
			var queryResult = GetAllData<RegionEntity>("Region", " 1=1 ORDER BY Country ",null, "DISTINCT Country");
			return queryResult.AsList();
		}
		/// <summary>
		/// 通过国家得到省份列表
		/// </summary>
		/// <param name="country"></param>
		/// <returns></returns>
		public IList<RegionEntity> GetProvinceListByCountry(string country)
		{
			var queryResult = GetAllData<RegionEntity>("Region", " and Country = @Country group by Province order by min(RegionID) ", new { Country = country }, "Province");
			return queryResult.AsList();
		}
		/// <summary>
		/// 通过省份得到城市列表
		/// </summary>
		/// <param name="province"></param>
		/// <returns></returns>
		public IList<RegionEntity> GetCityListByProvince(string province)
		{
			var queryResult = GetAllData<RegionEntity>("Region", " and Province = @Province group by City order by min(RegionID) ", new { Province = province }, "City");
			return queryResult.AsList();
		}
		/// <summary>
		/// 通过城市得到区县列表
		/// </summary>
		/// <param name="city"></param>
		/// <returns></returns>
		public IList<RegionEntity> GetAreaListByCity(string city)
		{
			var queryResult = GetAllData<RegionEntity>("Region", " and City = @City group by Area order by min(RegionID) ", new { City = city }, "Area");
			return queryResult.AsList();
		}
		/// <summary>
		/// 通过区县得到街道列表
		/// </summary>
		/// <param name="area"></param>
		/// <returns></returns>
		public IList<RegionEntity> GetArea1ListByArea(string area)
		{
			var queryResult = GetAllData<RegionEntity>("Region", " and Area = @Area order by area1", new { Area = area }, "DISTINCT Area1");
			return queryResult.AsList();
		}
		/// <summary>
		/// 通过街道得到小区列表
		/// </summary>
		/// <param name="area1"></param>
		/// <returns></returns>
		public IList<RegionEntity> GetArea2ListByArea1(string area1)
		{
			var queryResult = GetAllData<RegionEntity>("Region", " and Area1 = @Area1 order by area2", new { Area1 = area1 }, "DISTINCT Area2");
			return queryResult.AsList();
		}
	}
}
